﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Xml;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Client;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Metadata;
using Microsoft.Xrm.Sdk.Query;

namespace MSCRM.DataLoad
{
    public class DataLoad
    {
        CrmConnection _connection;
        CrmOrganizationServiceContext _context;

        public DataLoad() { }

        public DataLoad(CrmOrganizationServiceContext crmContext)
        {
            this._context = crmContext;
        }

        public event LogEventHandler LogEvent;
        public delegate void LogEventHandler(object sender, LogEventArgs e);

        public void Execute(string configfile, string connectionNumber = null)
        {
            try
            {
                logmessage(string.Format("loading config file '{0}'", configfile),false);
                XmlDocument xmldoc = new XmlDocument();
                xmldoc.Load(configfile);

                //setup crm connection
                XmlNodeList connectionNodes = xmldoc.SelectNodes("//Connection");

                SelectConnection:

                int connectionIndex = 0;
                string server;
                string org;

                if (string.IsNullOrEmpty(connectionNumber))
                {
                    foreach (XmlNode connectionNode in connectionNodes)
                    {
                        Console.ForegroundColor = ConsoleColor.Green;
                        server = connectionNode.Attributes["server"].Value;
                        org = connectionNode.Attributes["org"].Value;
                        Console.WriteLine(string.Format("[{0}] : Server={1} Org={2}", connectionIndex, server, org));
                        connectionIndex++;
                    }
                    Console.ResetColor();
                    Console.Write("Please select a connection from the list above:");
                    connectionIndex = int.Parse(Console.ReadLine());
                    if (connectionIndex > (connectionNodes.Count - 1))
                    {
                        Console.WriteLine("Please select a valid connection...");
                        goto SelectConnection;
                    }
                }
                else
                {
                    connectionIndex = int.Parse(connectionNumber);
                }

                server = connectionNodes[connectionIndex].Attributes["server"].Value;
                org = connectionNodes[connectionIndex].Attributes["org"].Value;
                string username = connectionNodes[connectionIndex].Attributes["username"].Value;
                string password = connectionNodes[connectionIndex].Attributes["password"].Value;
                string domain = connectionNodes[connectionIndex].Attributes["domain"].Value;
                string deviceid = string.Empty;
                if (connectionNodes[connectionIndex].Attributes["deviceid"] != null) deviceid = connectionNodes[connectionIndex].Attributes["deviceid"].Value;
                string devicepassword = string.Empty;
                if (connectionNodes[connectionIndex].Attributes["devicepassword"] != null) devicepassword = connectionNodes[connectionIndex].Attributes["devicepassword"].Value;
                string credString = string.Format("server={0}/{1};domain={2};username={3};password={4};deviceid={5};devicepassword={6}", server, org, domain, username, password, deviceid, devicepassword);
                Console.WriteLine("Attempting to connect:\n{0}", credString);
                _connection = CrmConnection.Parse(credString);
                _context = new CrmOrganizationServiceContext(_connection);
                Console.WriteLine(_connection.GetConnectionId());

                XmlNodeList jobs = xmldoc.SelectNodes("//Job");
                foreach (XmlNode job in jobs)
                {
                    string jobtype = job.Attributes["type"].Value;
                    string delimiter = ",";
                    if (job.Attributes["delimiter"] != null)
                    {
                        delimiter = job.Attributes["delimiter"].Value;
                    }
                    if (jobtype == "Export")
                    {
                        string filename = job.SelectSingleNode("File").InnerText;
                        string fetch = job.SelectSingleNode("Fetch").InnerText;
                        bool addReportColumns = false;
                        var reportColumns = job.SelectSingleNode("AddReportColumns");
                        if (reportColumns != null) addReportColumns = bool.Parse(reportColumns.InnerText);
                        logmessage(string.Format("Executing export to '{0}'", filename),false);
                        Export(filename, fetch, delimiter, addReportColumns);
                    }
                    else if(jobtype=="Import")
                    {
                        string filename = job.SelectSingleNode("File").InnerText;
                        string entity = job.SelectSingleNode("Entity").InnerText;
                        logmessage(string.Format("Executing import from '{0}'", filename),false);
                        Import(filename, entity, delimiter);
                    }
                    else if (jobtype == "NN")
                    {
                        string filename = job.SelectSingleNode("File").InnerText;
                        string entity1 = job.SelectSingleNode("Entity1").InnerText;
                        string entity2 = job.SelectSingleNode("Entity2").InnerText;
                        string relationshipName = job.SelectSingleNode("RelationshipName").InnerText;
                        logmessage(string.Format("Creating NN from '{0}'", filename), false);
                        CreateNN(filename, entity1, entity2, relationshipName, delimiter);
                    }
                    else if (jobtype == "UpdateStatus")
                    {
                        string filename = job.SelectSingleNode("File").InnerText;
                        string entityName = job.SelectSingleNode("Entity").InnerText;
                        logmessage(string.Format("Updating Status from '{0}'", filename), false);
                        UpdateStatus(filename, entityName, delimiter);
                    }
                    else
                    {
                        throw new Exception("Job type not recognised!");
                    }
                }
            }
            catch (Exception ex)
            {
                logmessage(ex.Message, true);
                logmessage(ex.StackTrace, true);
                if (ex.InnerException != null)
                {
                    logmessage(ex.InnerException.Message, true);
                    logmessage(ex.InnerException.StackTrace, true);
                }
            }
        }

        private void Export(string file, string fetch, string delimiter, bool addReportColumns)
        {
            FetchXmlToQueryExpressionRequest fetchToQueryRequest = new FetchXmlToQueryExpressionRequest()
            {
                FetchXml = fetch
            };
            FetchXmlToQueryExpressionResponse fetchToQueryResponse = (FetchXmlToQueryExpressionResponse)_context.Execute(fetchToQueryRequest);
            Export(file, fetchToQueryResponse.Query, delimiter, addReportColumns);
        }

        public void Export(string file, QueryExpression query, string delimiter, bool addReportColumns)
        {
            bool moreRecords = true;
            List<string> columns = new List<string>();
            List<string> header = new List<string>();
            using (var writer = new CsvFileWriter(file))
            {
                string entityName = query.EntityName;
                var metadata = GetEntityMetaData(entityName);
                var optionDictionaries = new Dictionary<string, Dictionary<int?, string>>();
                
                columns.Clear();
                foreach (var h in query.ColumnSet.Columns)
                {
                    columns.Add(h);
                    header.Add(h);
                    AttributeMetadata hm = (AttributeMetadata)(from a in metadata.Attributes where a.LogicalName == h select a).FirstOrDefault();
                    string columnType = hm.GetType().ToString();
                    if (addReportColumns && (
                        columnType == "Microsoft.Xrm.Sdk.OptionSetValue" ||
                        columnType == "Microsoft.Xrm.Sdk.Metadata.StatusAttributeMetadata" ||
                        columnType == "Microsoft.Xrm.Sdk.Metadata.PicklistAttributeMetadata"))
                    {
                        Dictionary<int?, string> hd = new Dictionary<int?, string>();
                        OptionMetadataCollection hOptions = GetOptionsSetByAttribute(_context, entityName, h);
                        foreach (var option in hOptions)
                        {
                            hd.Add(option.Value, option.Label.UserLocalizedLabel.Label.ToString());
                        }
                        optionDictionaries.Add(h, hd);
                        columns.Add(h + ".value");
                        header.Add(h + ".value");
                    }
                    else if (addReportColumns && (
                             columnType == "Microsoft.Xrm.Sdk.Metadata.LookupAttributeMetadata"))
                    {
                        columns.Add(h + ".name");
                        header.Add(h + ".name");
                    }
                }
                
                var linkedMetadata = new Dictionary<string, EntityMetadata>();
                var linkedColumnMetadata = new Dictionary<string, AttributeMetadata>();

                foreach (var le in query.LinkEntities)
                {
                    string linkedEntityName = le.LinkToEntityName;
                    EntityMetadata linkMeta = GetEntityMetaData(linkedEntityName);
                    linkedMetadata.Add(linkedEntityName, linkMeta);
                    foreach (var h in le.Columns.Columns)
                    {
                        string colName = le.LinkToEntityName.ToString() + "." + h;
                        columns.Add(colName);
                        header.Add(colName);
                        AttributeMetadata hm = (AttributeMetadata)(from a in linkMeta.Attributes where a.LogicalName == h select a).FirstOrDefault();
                        string columnType = hm.GetType().ToString();
                        if (addReportColumns && (
                            columnType == "Microsoft.Xrm.Sdk.OptionSetValue" ||
                            columnType == "Microsoft.Xrm.Sdk.Metadata.StatusAttributeMetadata" ||
                            columnType == "Microsoft.Xrm.Sdk.Metadata.PicklistAttributeMetadata"))
                        {
                            Dictionary<int?, string> hd = new Dictionary<int?, string>();
                            OptionMetadataCollection hOptions = GetOptionsSetByAttribute(_context, linkedEntityName, h);
                            foreach (var option in hOptions)
                            {
                                hd.Add(option.Value, option.Label.UserLocalizedLabel.Label.ToString());
                            }
                            optionDictionaries.Add(colName, hd);
                            columns.Add(colName + ".value");
                            header.Add(colName + ".value");
                        }
                        else if (addReportColumns && (
                                 columnType == "Microsoft.Xrm.Sdk.Metadata.LookupAttributeMetadata"))
                        {
                            columns.Add(h + ".name");
                            header.Add(h + ".name");
                        }

                    }
                }
                writer.WriteRow(columns);
                int pageCount = 1;
                while (moreRecords)
                {
                    logmessage(string.Format("Extracting page {0,-12} of {1}", pageCount++, file), false);
                    EntityCollection fetchCollection = _context.RetrieveMultiple(query);
                    writer.Delimiter = char.Parse(delimiter);
                    if (fetchCollection.Entities.Count > 0)
                    {
                        foreach (var e in fetchCollection.Entities)
                        {
                            columns.Clear();
                            foreach (var attributeName in query.ColumnSet.Columns)
                            {
                                if (e.Attributes.Contains(attributeName))
                                {
                                    columns.Add(cleanString(AttibuteValueToString(e.GetAttributeValue(attributeName))));
                                }
                                else
                                {
                                    columns.Add("");
                                }
                                if (addReportColumns && (header.Contains(attributeName + ".value")))
                                {
                                    Dictionary<int?, string> pickList = optionDictionaries[attributeName];
                                    columns.Add(cleanString(AttibuteValueToString(e.GetAttributeValue(attributeName), pickList)));
                                }
                                if (addReportColumns && (header.Contains(attributeName + ".name")))
                                {
                                    columns.Add(cleanString(AttibuteValueToString(e.GetAttributeValue(attributeName), null, true)));
                                }
                            }
                            foreach (var linkedEntity in query.LinkEntities)
                            {
                                foreach (var attributeName in linkedEntity.Columns.Columns)
                                {
                                    string aliasedAttributeName = linkedEntity.EntityAlias.ToString() + "." + attributeName;
                                    if (e.Attributes.Contains(aliasedAttributeName))
                                    {
                                        columns.Add(cleanString(AttibuteValueToString(((AliasedValue)e.GetAttributeValue(aliasedAttributeName)).Value)));
                                    }
                                    else
                                    {
                                        columns.Add("");
                                    }
                                    if (addReportColumns && (header.Contains(linkedEntity.LinkToEntityName.ToString() + "." + attributeName + ".value")))
                                    {
                                        Dictionary<int?, string> pickList = optionDictionaries[linkedEntity.LinkToEntityName.ToString() + "." + attributeName];
                                        var currValue = ((AliasedValue)e.GetAttributeValue(aliasedAttributeName));
                                        if (currValue != null)
                                            columns.Add(cleanString(AttibuteValueToString(currValue.Value, pickList)));
                                        else
                                            columns.Add(string.Empty);
                                    }
                                    if (addReportColumns && (header.Contains(linkedEntity.LinkToEntityName.ToString() + "." + attributeName + ".name")))
                                    {
                                        var currValue = ((AliasedValue)e.GetAttributeValue(aliasedAttributeName));
                                        if (currValue != null)
                                            columns.Add(cleanString(AttibuteValueToString(currValue.Value, null, true)));
                                        else
                                            columns.Add(string.Empty);
                                    }
                                }
                            }
                            writer.WriteRow(columns);
                        }
                    }
                    moreRecords = fetchCollection.MoreRecords;
                    if (moreRecords)
                    {
                        query.PageInfo.PageNumber++;
                        query.PageInfo.PagingCookie = fetchCollection.PagingCookie;
                    }
                }
            }
        }

        public void Import(string file, string entity, string delimiter)
        {
            int recCounter = 1;
            try
            {
                var metadata = GetEntityMetaData(entity);
                List<string> header = new List<string>();
                Entity newEntity = new Entity(entity);
                Guid entityId = Guid.Empty;
                using (var reader = new CsvFileReader(file))
                {
                    reader.Delimiter = char.Parse(delimiter);
                    reader.ReadRow(header);

                    List<string> columns = new List<string>();
                    while(reader.ReadRow(columns))
                    {
                        newEntity = new Entity(entity);
                        int columnIndex = 0;
                        foreach (var h in header)
                        {
                            AttributeMetadata attributemetadata = (AttributeMetadata)(from a in metadata.Attributes where a.LogicalName == h select a).FirstOrDefault();
                            object attributevalue = ValueToAttribute(columnIndex, columns, header, attributemetadata);
                            //logmessage(string.Format("{0,-12} {1}\t{2} - {3}", recCounter, file, columnIndex, attributevalue.GetType().ToString()), false);
                            if (attributemetadata.AttributeType == AttributeTypeCode.Uniqueidentifier)
                            {
                                entityId = (Guid)attributevalue;
/*
                                try
                                {
                                    entityId = (Guid)attributevalue;
                                }
                                catch (Exception ex)
                                {
                                    logmessage(string.Format("{0,-12} {1}\tProblems: {2}", recCounter, file, columnIndex), true);
                                }
 */
                                newEntity.Id = entityId;
                            }
                            if(attributevalue!=null)
                            {
                                newEntity.Attributes.Add(h, attributevalue);
                            }
                            columnIndex++;
                        }

                        Entity existingEntity = null;
                        try
                        {
                            if (entityId != Guid.Empty)
                            {
                                existingEntity = _context.Retrieve(entity, entityId, new ColumnSet());
                            }
                        }
                        catch { }

                        if (existingEntity != null)
                        {
                            logmessage(string.Format("{0,-12} {1}\tUpdating existing {2}", recCounter, file, newEntity.LogicalName),false);
                            if (_context.IsAttached(existingEntity)) _context.Detach(existingEntity);
                            _context.Update(newEntity);
                        }
                        else
                        {
                            logmessage(string.Format("{0,-12} {1}\tCreating new {2}", recCounter, file, newEntity.LogicalName), false);
                            _context.Create(newEntity);
                        }
                        recCounter++;
                    }
                }
            }
            catch (Exception ex)
            {
                logmessage(string.Format("{0,-12} {1}\tERROR!", recCounter, file),true);
                logmessage(ex.Message,true);
                logmessage(ex.StackTrace,true);
                if (ex.InnerException != null)
                {
                    logmessage(ex.InnerException.Message,true);
                    logmessage(ex.InnerException.StackTrace,true);
                }
            }
        }

        private void CreateNN(string file, string entity1, string entity2, string relationshipName, string delimiter)
        {
            int recCounter = 1;
            try
            {
                List<string> header = new List<string>();
                using (var reader = new CsvFileReader(file))
                {
                    reader.Delimiter = char.Parse(delimiter);
                    reader.ReadRow(header);

                    List<string> columns = new List<string>();
                    var field1 = header[0];
                    var field2 = header[1];

                    while (reader.ReadRow(columns))
                    {
                        AssociateRequest manyToMany = new AssociateRequest
                        {
                            Target = new EntityReference(entity1, Guid.Parse(columns[0])),
                            RelatedEntities = new EntityReferenceCollection {new EntityReference(entity2, Guid.Parse(columns[1]))},
                            Relationship = new Relationship(relationshipName)
                        };
                        logmessage(string.Format("{0,-12} {1}\tLinking \t{2} - {3}", recCounter, file, columns[0], columns[1]), false);
                        AssociateResponse ar = _context.Execute<AssociateResponse>(manyToMany);
                        recCounter++;
                    }
                }
            }
            catch (Exception ex)
            {
                logmessage(string.Format("{0,-12} {1}\tERROR!", recCounter, file), true);
                logmessage(ex.Message, true);
                logmessage(ex.StackTrace, true);
                if (ex.InnerException != null)
                {
                    logmessage(ex.InnerException.Message, true);
                    logmessage(ex.InnerException.StackTrace, true);
                }
            }
        }

        private void UpdateStatus(string file, string entityName, string delimiter)
        {
            int recCounter = 1;
            try
            {
                List<string> header = new List<string>();
                using (var reader = new CsvFileReader(file))
                {
                    reader.Delimiter = char.Parse(delimiter);
                    reader.ReadRow(header);

                    List<string> columns = new List<string>();
                    var entityId = header[0];
                    var stateCode = header[1];
                    var statusCode = header[2];

                    while (reader.ReadRow(columns))
                    {
                        Entity existingEntity = _context.Retrieve(entityName, Guid.Parse(columns[0]), new ColumnSet());
                        SetStateRequest state = new SetStateRequest();
                        state.State = new OptionSetValue(int.Parse(columns[1]));
                        state.Status = new OptionSetValue(int.Parse(columns[2]));
                        state.EntityMoniker = existingEntity.ToEntityReference();
                        logmessage(string.Format("{0,-12} {1}\tUpdating Status \t{2} - {3} - {4}", recCounter, file, columns[0], columns[1], columns[2]), false);
                        SetStateResponse stateSet = (SetStateResponse)_context.Execute(state);
                        recCounter++;
                    }
                }
            }
            catch (Exception ex)
            {
                logmessage(string.Format("{0,-12} {1}\tERROR!", recCounter, file), true);
                logmessage(ex.Message, true);
                logmessage(ex.StackTrace, true);
                if (ex.InnerException != null)
                {
                    logmessage(ex.InnerException.Message, true);
                    logmessage(ex.InnerException.StackTrace, true);
                }
            }
        }

        private EntityMetadata GetEntityMetaData(string logicalname)
        {
            RetrieveEntityRequest req = new RetrieveEntityRequest()
            {
                LogicalName = logicalname,
                RetrieveAsIfPublished = true,
                EntityFilters = Microsoft.Xrm.Sdk.Metadata.EntityFilters.Attributes
            };
            RetrieveEntityResponse resp = _context.Execute<RetrieveEntityResponse>(req);
            if (resp.Results.Count == 1)
            {
                EntityMetadata emd = (EntityMetadata)resp.Results.FirstOrDefault().Value;
                return emd;
            }
            return null;
        }

        private string cleanString(string stringtoclean)
        {
            if (stringtoclean == null) return null;
            return stringtoclean.Replace("\r", "").Replace("\n", "");
        }

        public static OptionMetadataCollection GetOptionsSetByAttribute(IOrganizationService service, string entityName, string attributeName)
        {
            var retrieveAttributeRequest = new RetrieveAttributeRequest();
            retrieveAttributeRequest.EntityLogicalName = entityName;
            retrieveAttributeRequest.LogicalName = attributeName;
            retrieveAttributeRequest.RetrieveAsIfPublished = true;
            var retrieveAttributeResponse = (RetrieveAttributeResponse)service.Execute(retrieveAttributeRequest);
            var optionMetadataCollection = (((EnumAttributeMetadata)retrieveAttributeResponse.AttributeMetadata).OptionSet).Options;
            return optionMetadataCollection;
        }

        private string AttibuteValueToString(object attribute, Dictionary<int?, string> pickList = null, bool resolveName = false)
        {
            string returnValue = string.Empty;
            if (attribute == null) return returnValue;

            switch (attribute.ToString())
            {
                case "Microsoft.Xrm.Sdk.OptionSetValue":
                    if (pickList == null)
                        returnValue = ((Microsoft.Xrm.Sdk.OptionSetValue)attribute).Value.ToString();
                    else
                        returnValue = pickList[((OptionSetValue)attribute).Value];
                    break;
                case "Microsoft.Xrm.Sdk.EntityReference":
                    if (resolveName) 
                        returnValue = ((Microsoft.Xrm.Sdk.EntityReference)attribute).Name;
                    else
                        returnValue = ((Microsoft.Xrm.Sdk.EntityReference)attribute).Id.ToString();
                    break;
                case "Microsoft.Xrm.Sdk.Money":
                    returnValue = ((Microsoft.Xrm.Sdk.Money)attribute).Value.ToString();
                    break;
                default:
                    returnValue = attribute.ToString();
                    break;
            }
            return returnValue;
        }

        private object ValueToAttribute(int columnIndex, List<string> columns, List<string> header, AttributeMetadata attributemetadata)
        {
            if (attributemetadata.EntityLogicalName == "annotation")
            {
                if (header[columnIndex] == "objectid"
                    && header.Contains("objectid")
                    && header.Contains("objecttypecode"))
                {
                    int objecttypecodeIndex = header.FindIndex(FindObjectTypeCodeColumnIndex);
                    return new EntityReference(columns[objecttypecodeIndex], Guid.Parse(columns[columnIndex]));
                }
            }
            return ValueToAttribute(columns[columnIndex], attributemetadata);
        }

        private bool FindObjectTypeCodeColumnIndex(string objecttypecode)
        {
            if (objecttypecode == "objecttypecode")
            {
                return true;
            }
            return false;
        }

        private object ValueToAttribute(string value, AttributeMetadata attributemetadata)
        {
            if (string.IsNullOrWhiteSpace(value)) return null;
            try
            {
                AttributeTypeCode attributetypecode = attributemetadata.AttributeType.Value;
                switch (attributetypecode)
                {
                    case AttributeTypeCode.Picklist:
                        return new OptionSetValue(int.Parse(value));
                    case AttributeTypeCode.BigInt:
                    case AttributeTypeCode.Integer:
                        return int.Parse(value);
                    case AttributeTypeCode.Boolean:
                        return bool.Parse(value);
                    case AttributeTypeCode.DateTime:
                        return DateTime.Parse(value);
                    case AttributeTypeCode.Decimal:
                        return decimal.Parse(value);
                    case AttributeTypeCode.Double:
                        return double.Parse(value);
                    case AttributeTypeCode.Lookup:
                        return new EntityReference(((LookupAttributeMetadata)attributemetadata).Targets[0], Guid.Parse(value));
                    case AttributeTypeCode.Memo:
                    case AttributeTypeCode.String:
                        return value;
                    case AttributeTypeCode.Money:
                        return new Money(decimal.Parse(value));
                    case AttributeTypeCode.Uniqueidentifier:
                        return Guid.Parse(value);
                    case AttributeTypeCode.Customer:
                        Guid customerid = Guid.Parse(value);
                        string entityname = GetCustomerFieldTypeEntityName(customerid);
                        if (!string.IsNullOrEmpty(entityname))
                        {
                            return new EntityReference(entityname, Guid.Parse(value));
                        }
                        else
                        {
                            return null;
                        }
                    default:
                        return null;
                }
            }
            catch
            {
                return null;
            }
        }

        private string GetCustomerFieldTypeEntityName(Guid customerid)
        {
            try
            {
                var c = _context.Retrieve("contact", customerid, new ColumnSet());
                if (c != null) return c.LogicalName;
            }
            catch { }
            try
            {
                var c = _context.Retrieve("account", customerid, new ColumnSet());
                if (c != null) return c.LogicalName;
            }
            catch { }
            return null;
        }

        private void logmessage(string message, bool iserror)
        {
            string iserrorstring = "INFO";
            if(iserror) iserrorstring = "ERROR";
            try
            {
                StreamWriter logfile = new StreamWriter("Nine.MSCRM.DataLoad.log", true);
                logfile.WriteLine("{0} - {1}: {2}",DateTime.Now.ToString(), iserrorstring, message);
                logfile.Close();
            }
            catch { }

            try
            {
                if (iserror)
                {
                    Console.ForegroundColor = ConsoleColor.Red;
                }
                Console.WriteLine(message);
                Console.ResetColor();
            }
            catch { }

            try
            {
                //fire log event
                LogEvent(this, new LogEventArgs(message, iserror));
            }
            catch { }
        }
    }

    public class LogEventArgs : EventArgs
    {
        private readonly string logmessage;
        private readonly bool iserror;

        public LogEventArgs(string logmessage, bool iserror)
        {
            this.logmessage = logmessage;
            this.iserror = iserror;
        }

        public string LogMessage
        {
            get { return this.logmessage; }
        }

        public bool IsError
        {
            get { return this.iserror; }
        }
    }
}
